<?php
/**
 * Created by PhpStorm.
 * User: liuhongchuan
 * Date: 2018/6/26
 * Time: 15:02
 */
namespace app\gym\controller;

use app\common\controller\AdminBase;

class Report extends AdminBase
{

    /**
     * 租柜列表
     */
    public function cabinet()
    {
        $shop_id =sp_get_current_shop_id();
        $auth_group = sp_get_auth_menu_3("10101000");
        $regions = db("gym_cabinet_region")->where(array("shop_id" => $shop_id, "status" => 1))->column("id, name");
        $this->assign('today', date("Y-m-d"));
        $this->assign("menus", $auth_group);
        $this->assign("regions", $regions);
        echo $this->fetch();
    }

    public function ajaxCabinet()
    {

        $this->ajax($this->_ajax_cabinet());
    }

    protected function _ajax_cabinet()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");
        $cabinet_no = input("cabinet_no/d");
        $region_id = input("region_id/d");
        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $opt_type = input('opt_type/d', 0);
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("co.shop_id" => $shop_id);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['o.card_no'] = array("like", "%{$card_no}%");
        }
        if (!empty($cabinet_no)) {
            $where['c.cabinet_no'] = $cabinet_no;
        }
        if (!empty($region_id)) {
            $where['c.region_id'] = $region_id;
        }
        if (!empty($opt_type)) {
            $where['co.opt_type'] = $opt_type;
        }

        if (!empty($start) && !empty($end)) {
            $where["co.order_date"] = array("between time", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["co.order_date"] = array(">= time", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["co.order_date"] = array("<= time", $end);
        }

        $total = db("gym_cabinet_order_history")->alias("co")
            ->join("m_gym_cabinet c", "c.id = co.cabinet_id", "left")
            ->join("m_gym_member m", "m.id = co.member_id", "left")
            ->join("m_gym_order o", "o.id = co.gym_order_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_cabinet_order_history")->alias("co")
                ->join("m_gym_cabinet c", "c.id = co.cabinet_id", "left")
                ->join("m_gym_member m", "m.id = co.member_id", "left")
                ->join("m_gym_order o", "o.id = co.gym_order_id", "left")
                ->join("m_gym_cabinet_region cr", "cr.id = c.region_id", "left")
                ->join("m_gym_employee e", "e.id = co.employee_id", "left")
                ->join("m_admin a", "a.id = co.admin_id", "left")
                ->field("co.id, co.opt_type, co.order_no, co.start_date, co.end_date, co.duration, co.money, co.order_date, co.create_time, co.desc, c.cabinet_no, cr.name as region_name, m.name as member_name, m.mobile, m.sex, o.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("co.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list =  db("gym_cabinet_order_history")->alias("co")
                ->join("m_gym_cabinet c", "c.id = co.cabinet_id", "left")
                ->join("m_gym_member m", "m.id = co.member_id", "left")
                ->join("m_gym_order o", "o.id = co.gym_order_id", "left")
                ->join("m_gym_cabinet_region cr", "cr.id = c.region_id", "left")
                ->join("m_gym_employee e", "e.id = co.employee_id", "left")
                ->join("m_admin a", "a.id = co.admin_id", "left")
                ->field("co.id, co.opt_type, co.order_no, co.start_date, co.end_date, co.duration, co.money, co.order_date, co.create_time, co.desc, c.cabinet_no, cr.name as region_name, m.name as member_name, m.mobile, m.sex, o.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("co.id desc")
                ->limit($this->get_limit())
                ->select();
        }

        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    /**
     * 导出
     */
    public function cabinetExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }

        $titles_des=array("租柜区域","租柜号","会员姓名","性别","手机号","合同号","合同有效期","收费金额", "状态", "操作人", "操作日期", '备注');
        $titles_field=array("region_name", "cabinet_no", "member_name","sex","mobile", "order_no", "effective_date", "money", "opt_type", "admin_name", "order_date", "desc");
        $data=$this->_ajax_cabinet();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['opt_type']==1){
                $list[$key]['opt_type']="录入";
            }elseif($value['opt_type']==2){
                $list[$key]['opt_type']="更换";
            }elseif($value['opt_type']==3){
                $list[$key]['opt_type']="转让";
            }elseif($value['opt_type']==4){
                $list[$key]['opt_type']="退柜";
            }
            $list[$key]['effective_date'] = $value['start_date'] . "-". $value['end_date'];
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
        }
        $title="租柜记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }


    /**
     * 请假
     */
    public function memberLeave()
    {
        $auth_group = sp_get_auth_menu_3("10101001");
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        echo $this->fetch();
    }

    public function ajaxLeave()
    {
        $this->ajax($this->_ajax_leave());
    }

    protected function _ajax_leave()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 8);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.end_date_desc, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, oh.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.end_date_desc, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, oh.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function leaveExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","请假类型","合同号","卡号","请假开始日期","请假结束日期", "请假天数", "收款金额", "操作人员", '操作日期', '备注');
        $titles_field=array("member_name","mobile","sex","leave_type","order_no","card_no","start_date","end_date", "leave_days", "pay_money", "admin_name", "order_date", "descs");
        $data=$this->_ajax_leave();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }

            if (empty($value['pay_money'])) {
                $list[$key]['leave_type']="非收费请假";
            } else {
                $list[$key]['leave_type']="收费请假";
            }

            $min_date = substr($value['end_date_desc'], 0, 10);
            $max_date = substr($value['end_date_desc'], -10, 10);
            $list[$key]['leave_days'] = (strtotime($max_date) - strtotime($min_date)) / 86400;
            $list[$key]['start_date'] = substr($value['create_time'], 0, 10);
            $list[$key]['end_date'] = date("Y-m-d",(strtotime( $list[$key]['start_date'])) + $list[$key]['leave_days'] * 86400);
        }
        $title="请假记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 销假
     */
    public function memberSickLeave()
    {
        $auth_group = sp_get_auth_menu_3("10101002");
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        echo $this->fetch();
    }

    public function ajaxSickLeave()
    {
        $this->ajax($this->_ajax_sick_leave());
    }

    protected function _ajax_sick_leave()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 9);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, oh.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, oh.card_no, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function sickLeaveExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别", "合同号","卡号", "销假日期", "归属会籍", "操作人员", '操作日期', '备注');
        $titles_field=array("member_name","mobile","sex","order_no","card_no","slick_leave_date", "employee_name", "admin_name", "order_date", "descs");
        $data=$this->_ajax_sick_leave();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            $list[$key]['slick_leave_date'] = substr($value['create_time'], 0, 10);
        }
        $title="销假记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }


    /**
     * 换卡
     */
    public function memberChangeCard()
    {
        $auth_group = sp_get_auth_menu_3("10101003");
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        echo $this->fetch();
    }

    public function ajaxChangeCard()
    {
        $this->ajax($this->_ajax_change_card());
    }

    protected function _ajax_change_card()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 6);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no|oh.old_card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.opt_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function changeCardExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","合同号", "合同有效期", "卡种名称","收费金额","原卡号","状态", "新卡号",  "操作人", '操作日期', '备注');
        $titles_field=array("member_name","mobile","sex","order_no","order_effective_date","gym_card_name","pay_money","old_card_no", "opt_type_name", "card_no", "admin_name", "order_date", "descs");
        $data=$this->_ajax_change_card();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "换卡";
        }
        $title="换卡记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 转卡
     */
    public function memberTransCard()
    {
        $auth_group = sp_get_auth_menu_3("10101008");
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        echo $this->fetch();
    }

    public function ajaxTransCard()
    {
        $this->ajax($this->_ajax_trans_card());
    }

    protected function _ajax_trans_card()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 7);
        if (!empty($member_name)) {
            $where['m.name|om.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile|om.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_gym_member om", "om.id = oh.old_gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_gym_member om", "om.id = oh.old_gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id,m.name as member_name, m.mobile, m.sex, oh.opt_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, om.name as old_member_name, om.mobile as old_mobile, om.sex as old_sex,oh.pay_money, oh.create_time, oh.descs, oh.order_date,  e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_gym_member om", "om.id = oh.old_gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id,m.name as member_name, m.mobile, m.sex, oh.opt_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, om.name as old_member_name, om.mobile as old_mobile, om.sex as old_sex,oh.pay_money, oh.create_time, oh.descs, oh.order_date,  e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function transCardExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("新会员姓名","手机号","性别","合同号", "合同有效期", "卡种名称", "原会员姓名","手机号","性别", "收费金额", "状态", "归属会籍", "操作人员", '操作日期', '备注');
        $titles_field=array("member_name","mobile","sex","order_no","order_effective_date","gym_card_name","old_member_name","old_mobile","old_sex","pay_money", "opt_type_name", "employee_name", "admin_name", "order_date", "descs");
        $data=$this->_ajax_trans_card();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            if($value['old_sex']==1){
                $list[$key]['old_sex']="男";
            }else{
                $list[$key]['old_sex']="女";
            }
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "转卡";
        }
        $title="转卡记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 退会
     */
    public function memberOut()
    {
        $auth_group = sp_get_auth_menu_3("10101004");
        $this->assign('today', date("Y-m-d"));
        $this->assign("menus", $auth_group);
        echo $this->fetch();
    }

    public function ajaxMemberOut()
    {
        $this->ajax($this->_ajax_member_out());
    }

    protected function _ajax_member_out()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 5);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function outExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","合同号", "合同有效期", "卡种名称", '退会时间', "收费金额", "状态", "操作人", '备注');
        $titles_field=array("member_name","mobile","sex","order_no","order_effective_date","gym_card_name", "order_date", "pay_money", "opt_type_name", "admin_name", "descs");
        $data=$this->_ajax_member_out();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "退会";
        }
        $title="退会记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }


    /**
     * 退款重办
     */
    public function memberInAgain()
    {
        $auth_group = sp_get_auth_menu_3("10101005");
        $this->assign('today', date("Y-m-d"));
        $this->assign("menus", $auth_group);
        echo $this->fetch();
    }

    public function ajaxMemberInAgain()
    {
        $this->ajax($this->_ajax_member_in_again());
    }

    protected function _ajax_member_in_again()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 4);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function inAgainExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","合同号", "合同有效期", "卡种名称","合同金额","归属会籍",  "状态","操作人", '操作时间', '备注');
        $titles_field=array("member_name","mobile","sex","order_no","order_effective_date","gym_card_name","pay_money", "employee_name", "opt_type_name", "admin_name", "order_date", "descs");
        $data=$this->_ajax_member_in_again();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "退款重办";
        }
        $title="退款重办记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 会员升级
     */
    public function memberUp()
    {
        $auth_group = sp_get_auth_menu_3("10101006");
        $this->assign('today', date("Y-m-d"));
        $this->assign("menus", $auth_group);
        echo $this->fetch();
    }

    public function ajaxMemberUp()
    {
        $this->ajax($this->_ajax_member_up());
    }

    protected function _ajax_member_up()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 2);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name,oh.gym_card_type,oh.able_count_desc, oh.able_money_desc, oh.able_day_desc, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name,oh.gym_card_type,oh.able_count_desc, oh.able_money_desc, oh.able_day_desc, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function upExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","合同号", "原合同有效期", "原剩余天数/次数/金额", "现合同有效期", "现剩余天数/次数/金额", "交易金额","归属会籍",  "状态","操作人员", '操作日期', '备注');
        $titles_field=array("member_name","mobile","sex","order_no","old_order_effective_date","old_able_desc", "order_effective_date", "able_desc","pay_money", "employee_name", "opt_type_name", "admin_name", "order_date", "descs");
        $data=$this->_ajax_member_up();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            if ($value['gym_card_type'] == 1) {
                $able_desc = explode("->", $value['able_count_desc']);

            } elseif ($value['gym_card_type'] == 2) {
                $able_desc = explode("->", $value['able_day_desc']);
            } elseif ($value['gym_card_type'] == 3)  {
                $able_desc = explode("->", $value['able_money_desc']);
            }
            $list[$key]['old_able_desc'] = trim($able_desc[0]);
            $list[$key]['able_desc'] = trim($able_desc[1]);

            $list[$key]['old_order_effective_date'] = substr($value['start_date_desc'], 0, 10) . "-" . substr($value['end_date_desc'], 0, 10);
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "升级";
        }
        $title="升级记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }


    /**
     * 续卡
     */
    public function memberContinue()
    {
        $auth_group = sp_get_auth_menu_3("10101007");
        $this->assign('today', date("Y-m-d"));
        $this->assign("menus", $auth_group);
        echo $this->fetch();
    }

    public function ajaxMemberContinue()
    {
        $this->ajax($this->_ajax_member_continue());
    }

    protected function _ajax_member_continue()
    {
        $shop_id =sp_get_current_shop_id();
        $member_name = input("member_name/s");
        $member_mobile = input("member_mobile/d");
        $card_no = input("card_no");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id, 'oh.operation_type' => 3);
        if (!empty($member_name)) {
            $where['m.name'] = array("like", "%{$member_name}%");
        }
        if (!empty($member_mobile)) {
            $where['m.mobile'] = array("like", "%{$member_mobile}%");
        }
        if (!empty($card_no)) {
            $where['oh.card_no'] = array("like", "%{$card_no}%");
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
            ->join("m_admin a", "a.id = oh.admin_id", "left")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no, oh.start_date_desc, oh.end_date_desc, oh.gym_card_name, oh.card_no, oh.old_card_no, oh.pay_money, oh.create_time, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function continueExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("会员姓名","手机号","性别","合同号","卡种名称", "续卡合同有效期",  "合同金额","归属会籍",  "状态","操作人", '操作时间', '备注');
        $titles_field=array("member_name","mobile","sex","order_no", "gym_card_name", "order_effective_date","pay_money", "employee_name", "opt_type_name", "admin_name", "order_date", "descs");
        $data=$this->_ajax_member_continue();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if($value['sex']==1){
                $list[$key]['sex']="男";
            }else{
                $list[$key]['sex']="女";
            }
            $list[$key]['order_effective_date'] = substr($value['start_date_desc'], -10, 10) . "-" . substr($value['end_date_desc'], -10, 10);
            $list[$key]['opt_type_name'] = "续卡";
        }
        $title="续卡记录";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 会籍合同
     */
    public function mcOrder()
    {
        $auth_group = sp_get_auth_menu_3("10111000");
        $shop_id = sp_get_current_shop_id();
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        $this->assign("cards", db("GymCard")->where("shop_id=$shop_id and status=1")->field("id,card_name")->select());
        $this->assign("mcs", db("GymEmployee")->where("shop_id=$shop_id and status=1 and type=2")->field("id,name")->select());
        echo $this->fetch();
    }

    public function ajaxMcOrder()
    {
        $this->ajax($this->_ajax_mc_order());
    }

    protected function _ajax_mc_order()
    {
        $shop_id =sp_get_current_shop_id();
        $member_ship_id = input("member_ship_id");
        $operation_type = input("operation_type");
        $gym_card_id = input("gym_card_id");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id);
        if (!empty($member_ship_id)) {
            $where['oh.member_ship_id'] = $member_ship_id;
        }
        if (!empty($operation_type)) {
            $where['oh.operation_type'] = $operation_type;
        }
        if (!empty($gym_card_id)) {
            $where['oh.gym_card_id'] = $gym_card_id;
        }

        if (!empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("between", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $where["oh.order_date"] = array(">=", $start);
        } elseif (empty($start) && !empty($end)) {
            $where["oh.order_date"] = array("<=", $end);
        }

        $total = db("gym_order_history")->alias("oh")
            ->where($where)
            ->field('count(1) as count, sum(pay_money) as price')
            ->find();
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no,oh.gym_card_name, oh.pay_money, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.gym_member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.member_ship_id", "left")
                ->field("oh.id, oh.operation_type, oh.order_no,oh.gym_card_name, oh.pay_money, oh.descs, oh.order_date, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total['count'], 'draw' => $draw, 'money' => !empty($total['price']) ? $total['price'] : 0);
    }

    public function mcExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("合同号","会员姓名","性别","电话", "卡种", "交易金额", "状态","归属MC",  "操作时间","操作人");
        $titles_field=array("order_no","member_name","sex","mobile","gym_card_name","pay_money", "operation_type", "employee_name", "order_date", "admin_name");
        $data=$this->_ajax_mc_order();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if ($value['sex']==1) {
                $list[$key]['sex'] = "男";
            } else {
                $list[$key]['sex'] = "女";
            }
            switch ($value['operation_type']) {
                case 1:
                    $list[$key]['operation_type'] = "购卡";
                    break;
                case 2:
                    $list[$key]['operation_type'] = "升级";
                    break;
                case 3:
                    $list[$key]['operation_type'] = "续卡";
                    break;
                case 4:
                    $list[$key]['operation_type'] = "退款重办";
                    break;
                case 5:
                    $list[$key]['operation_type'] = "退会";
                    break;
                case 6:
                    $list[$key]['operation_type'] = "换卡";
                    break;
                case 7:
                    $list[$key]['operation_type'] = "转卡";
                    break;
                case 8:
                    $list[$key]['operation_type'] = "请假";
                    break;
                case 9:
                    $list[$key]['operation_type'] = "销假";
                    break;
                default:
                    $list[$key]['operation_type'] = "";
                    break;

            }
        }
        $title = "会籍合同";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 私教合同
     */
    public function ptOrder()
    {
        $auth_group = sp_get_auth_menu_3("10111001");
        $shop_id = sp_get_current_shop_id();
        $this->assign("menus", $auth_group);
        $this->assign('today', date("Y-m-d"));
        $this->assign("courses", db("gym_course")->where("shop_id=$shop_id and status=1")->field("id,name")->select());
        $this->assign("pts", db("GymEmployee")->where("shop_id=$shop_id and status=1 and type=3")->field("id,name")->select());
        echo $this->fetch();
    }

    public function ajaxPtOrder()
    {
        $this->ajax($this->_ajax_pt_order());
    }

    protected function _ajax_pt_order()
    {
        $shop_id =sp_get_current_shop_id();
        $pt_id = input("pt_id/d");
        $type = input("type/d");
        $course_id = input("course_id/d");

        $draw = input("draw",0)+1; //请求时间
        $start = input("start/s");
        $end = input("end/s");
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("oh.shop_id" => $shop_id);
        if (!empty($pt_id)) {
            $where['oh.pt_id'] = $pt_id;
        }
        if (!empty($type)) {
            $where['oh.type'] = $type;
        }
        if (!empty($course_id)) {
            $where['oh.course_id'] = $course_id;
        }

        if (!empty($start) && !empty($end)) {
            $start .= " 00:00:00";
            $end .= " 23:59:59";
            $where["oh.create_time"] = array("between time", array($start, $end));
        } elseif (!empty($start) && empty($end)) {
            $start .= " 00:00:00";
            $where["oh.create_time"] = array(">= time", $start);
        } elseif (empty($start) && !empty($end)) {
            $end .= " 23:59:59";
            $where["oh.create_time"] = array("<= time", $end);
        }

        $total = db("gym_pt_order_history")->alias("oh")
            ->where($where)
            ->field('count(1) as count, sum(price) as price')
            ->find();
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_pt_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.pt_id", "left")
                ->field("oh.id, oh.order_no, oh.type, oh.create_time,oh.price, oh.course_name, oh.course_count, oh.type, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_pt_order_history")->alias("oh")
                ->join("m_gym_member m", "m.id = oh.member_id", "left")
                ->join("m_admin a", "a.id = oh.admin_id", "left")
                ->join("m_gym_employee e", "e.id = oh.pt_id", "left")
                ->field("oh.id,  oh.order_no, oh.type, oh.create_time,oh.price, oh.course_name, oh.course_count, oh.type, m.name as member_name, m.mobile, m.sex, e.name as employee_name, a.name as admin_name")
                ->where($where)
                ->order("oh.id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total['count'], 'draw' => $draw, 'money' => !empty($total['price']) ? $total['price'] : 0);
    }

    public function ptExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("合同号","会员姓名","性别","电话", "课程名称","课时数","交易金额", "状态","归属PT",  "操作时间","操作人");
        $titles_field=array("order_no","member_name","sex","mobile","course_name", "course_count","price", "type", "employee_name", "create_time", "admin_name");
        $data=$this->_ajax_pt_order();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if ($value['sex']==1) {
                $list[$key]['sex'] = "男";
            } else {
                $list[$key]['sex'] = "女";
            }
            switch ($value['type']) {
                case 1:
                    $list[$key]['type'] = "购课";
                    break;
                case 2:
                    $list[$key]['type'] = "转让";
                    break;
                case 3:
                    $list[$key]['type'] = "退课";
                    break;
                default:
                    $list[$key]['type'] = "";
                    break;

            }
            $list[$key]['create_time'] = substr($value['create_time'], 0, 10);
        }
        $title = "私教合同";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }

    /**
     * 人力报表
     */
    public function employee()
    {
        $auth_group = sp_get_auth_menu_3("10111002");
        $this->assign("menus", $auth_group);
        echo $this->fetch();
    }

    public function ajaxEmployee()
    {
        $this->ajax($this->_ajax_employee());
    }

    protected function _ajax_employee()
    {
        $shop_id =sp_get_current_shop_id();
        $status = input("status/d");
        $type = input("type/d");
        $name = input("name/s");
        $mobile = input("mobile/s");
        $draw = input("draw",0)+1; //请求时间
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);

        $where = array("shop_id" => $shop_id);

        if (!empty($status)) {

            $where['status'] = $status == 1 ? 1 : 0;
        }
        if (!empty($type)) {
            $where['type'] = $type;
        }
        if (!empty($name)) {
            $where['name'] = array("like", "%$name%");
        }
        if (!empty($mobile)) {
            $where['mobile'] = array("like", "%$mobile%");
        }



        $total = db("gym_employee")
            ->where($where)
            ->count(1);
        if (!empty($start_row) && !empty($end_row)) {
            $list =  db("gym_employee")
                ->field("name, sex, mobile, type, web_open_up, create_time")
                ->where($where)
                ->order("id desc")
                ->limit($start_row - 1, $end_row - $start_row + 1)
                ->select();
        } else {
            $list = db("gym_employee")
                ->field("name, sex, mobile, type, web_open_up, create_time")
                ->where($where)
                ->order("id desc")
                ->limit($this->get_limit())
                ->select();
        }
        return array('pageData' => $list, 'total' => $total, 'draw' => $draw);
    }

    public function employeeExcel()
    {
        $start_row = input('start_row/d', 0);
        $end_row = input('end_row/d', 0);
        if ($start_row > $end_row) {
            die;
        }
        if ($end_row - $start_row > 10000) {
            die;
        }
        $titles_des=array("姓名","性别","手机号","职务", "是否开通手机端","创建时间");
        $titles_field=array("name","sex","mobile","type","web_open_up", "create_time");
        $data=$this->_ajax_employee();
        $list=$data['pageData'];
        foreach ($list as $key => $value) {
            if ($value['sex']==1) {
                $list[$key]['sex'] = "男";
            } else {
                $list[$key]['sex'] = "女";
            }

            if ($value['type']==2) {
                $list[$key]['type'] = "MC";
            } elseif ($value['type'] == 3) {
                $list[$key]['type'] = "PT";
            }

            if ($value['web_open_up']==1) {
                $list[$key]['web_open_up'] = "是";
            } else {
                $list[$key]['web_open_up'] = "否";
            }
        }
        $title = "人力报表";
        var_dump(export_excel($titles_des,$titles_field,$list,$title));die;
        die;
    }
}